﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;
namespace DAO
{
    public class MonAnDAO
    {
        public string TenMonAn(int Ma)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<MonAnDTO> KQ = new List<MonAnDTO>();
            string sql = "select TenMon from MonAn where MaMonAn=" + Ma;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            string kq;
            try
            {
                kq = cmd.ExecuteScalar().ToString();
            }
            catch
            {
                conn.Close();
                return "";
            }
            conn.Close();
            return kq;
        
        }
        public int ThemMonAn(MonAnDTO mnDTO)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "INSERT INTO MonAn(MaMonAn, MaLoai, TenMon, GiaBD, GiaNH, DonVi) VALUES("+mnDTO.MaMonAn+", "+mnDTO.MaLoai+", N'"+mnDTO.TenMon+"', "+mnDTO.GiaBD+", "+mnDTO.GiaNH+", N'"+mnDTO.DonVi+"')";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int CapNhatMonAn(MonAnDTO mn)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "Update MonAn set MaLoai="+mn.MaLoai+", TenMon=N'"+mn.TenMon+"', GiaBD="+mn.GiaBD+", GiaNH="+mn.GiaNH+", DonVi=N'"+mn.DonVi+"' where MaMonAn="+mn.MaMonAn;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int XoaMonAn(int MaMA)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "Delete MonAn where MaMonAn=" + MaMA ;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = -1;
            try
            {
                kq = cmd.ExecuteNonQuery();
            }
            catch
            {
                conn.Close();
                return 0;
            }
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int LayMaMonAn()
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "select top 1 MaMonAn from MonAn order by MaMonAn desc";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq;
            try
            {
                kq = int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch
            {
                conn.Close();
                return 0;
            }
            conn.Close();
            return kq;
        }
        public List<MonAnDTO> LayDanhSachMonAn()
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<MonAnDTO> KQ = new List<MonAnDTO>();
            string sql = "select * from MonAn";
            SqlDataAdapter dtadp = new SqlDataAdapter(sql, conn);
            try
            {
                DataTable dt = new DataTable();
                dtadp.Fill(dt);
                MonAnDTO cn;
                foreach (DataRow dtrow in dt.Rows)
                {
                    cn = new MonAnDTO();
                    cn.MaMonAn = int.Parse(dtrow["MaMonAn"].ToString());
                    cn.MaLoai = int.Parse(dtrow["MaLoai"].ToString());
                    cn.TenMon = dtrow["TenMon"].ToString();
                    cn.GiaBD = int.Parse(dtrow["GiaBD"].ToString());
                    cn.GiaNH = int.Parse(dtrow["GiaNH"].ToString());
                    cn.DonVi = dtrow["DonVi"].ToString();
                    KQ.Add(cn);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return KQ;
        }
        //ket noi cho tra cuu mon an theo ten loai mon an 
        public List<MonAnDTO> Laymonan(int MaLoai)
        {
            string sql = "select * from MonAn where MaLoai=" + MaLoai;
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<MonAnDTO> kq = new List<MonAnDTO>();
            SqlDataAdapter ds = new SqlDataAdapter(sql, conn);
            try
            {
                DataTable table = new DataTable();
                ds.Fill(table);
                MonAnDTO ma;
                foreach (DataRow rows in table.Rows)
                {
                    ma = new MonAnDTO();
                    ma.MaMonAn = int.Parse(rows["MaMonAn"].ToString());
                    ma.MaLoai = int.Parse(rows["MaLoai"].ToString());
                    ma.TenMon = rows["TenMon"].ToString();
                    ma.GiaBD = int.Parse(rows["GiaBD"].ToString());
                    ma.GiaNH = int.Parse(rows["GiaNH"].ToString());
                    ma.DonVi = rows["DonVi"].ToString();
                    kq.Add(ma);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return kq;
        }
        //tra cuu mon an theo gia nha hang, gia binh dan
        //tra cuu theo gia nha hang
        public List<MonAnDTO> Laymonantheogianhahang(float giabatdau, float giaketthuc)
        {
            string sql = "select MaMonAn,MaLoai,TenMon,GiaNH,DonVi from MonAn where GiaNH >=" + giabatdau + "and GiaNH <=" + giaketthuc;
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<MonAnDTO> kq = new List<MonAnDTO>();
            SqlDataAdapter ds = new SqlDataAdapter(sql, conn);

            try
            {
                DataTable table = new DataTable();
                ds.Fill(table);
                MonAnDTO ma;
                foreach (DataRow rows in table.Rows)
                {
                    ma = new MonAnDTO();
                    ma.MaMonAn = int.Parse(rows["MaMonAn"].ToString());
                    ma.MaLoai = int.Parse(rows["MaLoai"].ToString());
                    ma.TenMon = rows["TenMon"].ToString();
                    ma.GiaNH = int.Parse(rows["GiaNH"].ToString());
                    ma.DonVi = rows["DonVi"].ToString();
                    kq.Add(ma);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return kq;
        }
        //tra cuu theo gia binh dan
        public List<MonAnDTO> Laymonantheogiabinhdan(float giabatdau, float giaketthuc)
        {
            string sql = "select * from MonAn where GiaBD >=" + giabatdau + "and GiaBD <=" + giaketthuc;
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<MonAnDTO> kq = new List<MonAnDTO>();
            SqlDataAdapter ds = new SqlDataAdapter(sql, conn);
            try
            {
                DataTable table = new DataTable();
                ds.Fill(table);
                MonAnDTO ma;
                foreach (DataRow rows in table.Rows)
                {
                    ma = new MonAnDTO();
                    ma.MaMonAn = int.Parse(rows["MaMonAn"].ToString());
                    ma.MaLoai = int.Parse(rows["MaLoai"].ToString());
                    ma.TenMon = rows["TenMon"].ToString();
                    ma.GiaBD = float.Parse(rows["GiaBD"].ToString());
                    ma.DonVi = rows["DonVi"].ToString();
                    kq.Add(ma);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return kq;
        }
        public float LayGiaMonAnTheoMaBan(string MaBan, int MaMonAn)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "";
            if (MaBan.Substring(0, 2) == "BD")
            {
                sql = "select GiaBD from MonAN where MaMonAn = " + MaMonAn;
            }
            else
            {
                sql = "select GiaNH from MonAN where MaMonAn = " + MaMonAn;
            }
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            float kq;
            try
            {
                kq = float.Parse(cmd.ExecuteScalar().ToString());
            }
            catch
            {
                conn.Close();
                return 0;
            }
            conn.Close();
            return kq;
        }
        //kiem tra mon an
        public int KiemTraMonAn(int MaMN)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "select MaMonAn from ChiTietHoaDon where MaMonAn=" + MaMN;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = -1;
            try
            {
                kq = int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch
            {
                conn.Close();
                return 0;
            }
            conn.Close();
            if (kq >= 0)
                return 1;
            return 0;
        }
    }
}
